package cn.mq;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import static cn.mq.constant.*;


public class InsertIntoMysqlUtil {
    private static final Logger logger = Logger.getLogger(InsertIntoMysqlUtil.class); // 创建 Logger 实例

    /**
     * 将发送批售信息插入到Mysql
     *
     * @param jsonString
     */
    public static void insertSendWholesaleInfo(String jsonString) {
        JSONObject jsonObj = JSON.parseObject(jsonString);
        JSONObject requestHead = jsonObj.getJSONObject("requestHead");
        JSONArray dataArray = jsonObj.getJSONObject("requestBody").getJSONArray("data");
        JSONObject requestBody = dataArray.getJSONObject(0);

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "insert into SendWholesaleInfo" +
                    " (requestId,sourceSystem,targetSystem,serviceName,sendTime,brand,bolType,seqNo,bizCode,bizType," +
                    "carOmdId,vin,seriesCode,modelCode,modelVersion,colorCode,interiorCode,optionCode,dealerCode," +
                    "dealerName,salesStatus,salesRetFlag,rateCode,rateType,rsscCode,modelYear,bulkOrderDate) " +
                    "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, requestHead.getString("requestId"));
                stmt.setString(2, requestHead.getString("sourceSystem"));
                stmt.setString(3, requestHead.getString("targetSystem"));
                stmt.setString(4, requestHead.getString("serviceName"));
                stmt.setString(5, requestHead.getString("sendTime"));

                stmt.setString(6, requestBody.getString("brand"));
                stmt.setString(7, requestBody.getString("bolType"));
                stmt.setString(8, requestBody.getString("seqNo"));
                stmt.setString(9, requestBody.getString("bizCode"));
                stmt.setString(10, requestBody.getString("bizType"));
                stmt.setString(11, requestBody.getString("carOmdId"));
                stmt.setString(12, requestBody.getString("vin"));
                stmt.setString(13, requestBody.getString("seriesCode"));
                stmt.setString(14, requestBody.getString("modelCode"));
                stmt.setString(15, requestBody.getString("modelVersion"));
                stmt.setString(16, requestBody.getString("colorCode"));
                stmt.setString(17, requestBody.getString("interiorCode"));
                stmt.setString(18, requestBody.getString("optionCode"));
                stmt.setString(19, requestBody.getString("dealerCode"));
                stmt.setString(20, requestBody.getString("dealerName"));
                stmt.setString(21, requestBody.getString("salesStatus"));
                stmt.setString(22, requestBody.getString("salesRetFlag"));
                stmt.setString(23, requestBody.getString("rateCode"));
                stmt.setString(24, requestBody.getString("rateType"));
                stmt.setString(25, requestBody.getString("rsscCode"));
                stmt.setString(26, requestBody.getString("modelYear"));
                stmt.setString(27, requestBody.getString("bulkOrderDate"));

                stmt.executeUpdate();
                stmt.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将电子发票数据插入到Mysql
     *
     * @param jsonString
     */
    public static void insertSendElectronicInvoice(String jsonString) {

        JSONObject jsonObj = JSON.parseObject(jsonString);
        logger.info("********************************************************************************************************************************");
        logger.info("********************************************************************************************************************************");
        logger.info("********************************************************************************************************************************");
        // 获取requestHead
        JSONObject requestHead = jsonObj.getJSONObject("requestHead");
        // 获取requestBody的data数组
        JSONArray jsonArray = jsonObj.getJSONObject("requestBody").getJSONArray("data");

        // 创建新的data数组，只保留需要的字段
        JSONArray newDataArray = new JSONArray();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject originalData = jsonArray.getJSONObject(i);
            JSONObject newData = new JSONObject();
            newData.put("invoiceCode", originalData.getString("invoiceCode"));
            newData.put("invoiceNo", originalData.getString("invoiceNo"));
            newData.put("invoiceEngineCode", originalData.getString("invoiceEngineCode"));
            newDataArray.add(newData);
        }
        // 构建新的JSON结构
        JSONObject newJson = new JSONObject();
        newJson.put("requestHead", requestHead);
        JSONObject newRequestBody = new JSONObject();
        newRequestBody.put("data",newDataArray);
        newJson.put("requestBody", newRequestBody);
        // 输出日志
        logger.info(newJson.toJSONString());
        logger.info("********************************************************************************************************************************");
        logger.info("********************************************************************************************************************************");
        logger.info("********************************************************************************************************************************");



        JSONArray dataArray = jsonObj.getJSONObject("requestBody").getJSONArray("data");

        logger.info("dataArray.size() = " + dataArray.size());


        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "replace into ods_omd_electronicInvoice_df" +
                    " ( invoiceCode,invoiceNo,invoiceEngineCode," +
                    "taxRate,taxAmount,totalAmount,actualAmount,invoiceDate,vehicleType,factoryPlateModel,certNo,engineNo," +
                    "vin,producePlace,salesTaxNo,salesName,salesAddressTel,salesBankAccount,purchaseName,purchaseTaxNo," +
                    "purchaseIdNo,deleteFlag,deleteDate,month,uploadDate,remark,taxOfficeCode,machineNo,account," +
                    "invoiceBy,payeeBy,reviewBy,mainGoodsName,goodsTaxItem,redNoticeNo,submitFlag,checkCode,importProveNo" +
                    ",commodityInspectionNo,limitCount,tonnage,industryCode,tel,factoryName,sendBatchNo,brandCode," +
                    "identityCode,modelCode,modelYear,modelVersion,interiorCode,colorCode,prList,dealerNetCode," +
                    "regionCode,invoiceType,salesValidFlag,subId ) values (?,?,?,?,?,?,?,?,?,?,?,?,lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))),?,?,?,?,?," +
                    "lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))),lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {

                for (int i = 0; i < dataArray.size() ; i++) {
                    JSONObject requestBody = dataArray.getJSONObject(i);


                    stmt.setString(1, requestBody.getString("invoiceCode"));
                    stmt.setString(2, requestBody.getString("invoiceNo"));
                    stmt.setString(3, requestBody.getString("invoiceEngineCode"));
                    stmt.setString(4, requestBody.getString("taxRate"));
                    stmt.setString(5, requestBody.getString("taxAmount"));
                    stmt.setString(6, requestBody.getString("totalAmount"));
                    stmt.setString(7, requestBody.getString("actualAmount"));
                    stmt.setString(8, requestBody.getString("invoiceDate"));
                    stmt.setString(9, requestBody.getString("vehicleType"));
                    stmt.setString(10, requestBody.getString("factoryPlateModel"));
                    stmt.setString(11, requestBody.getString("certNo"));
                    stmt.setString(12, requestBody.getString("engineNo"));
                    stmt.setString(13, requestBody.getString("vin"));
                    stmt.setString(14, requestBody.getString("producePlace"));
                    stmt.setString(15, requestBody.getString("salesTaxNo"));
                    stmt.setString(16, requestBody.getString("salesName"));
                    stmt.setString(17, requestBody.getString("salesAddressTel"));
                    stmt.setString(18, requestBody.getString("salesBankAccount"));
                    stmt.setString(19, requestBody.getString("purchaseName"));
                    stmt.setString(20, requestBody.getString("purchaseTaxNo"));
                    stmt.setString(21, requestBody.getString("purchaseIdNo"));
                    stmt.setString(22, requestBody.getString("deleteFlag"));
                    stmt.setString(23, requestBody.getString("deleteDate"));
                    stmt.setString(24, requestBody.getString("month"));
                    stmt.setString(25, requestBody.getString("uploadDate"));
                    stmt.setString(26, requestBody.getString("remark"));
                    stmt.setString(27, requestBody.getString("taxOfficeCode"));
                    stmt.setString(28, requestBody.getString("machineNo"));
                    stmt.setString(29, requestBody.getString("account"));
                    stmt.setString(30, requestBody.getString("invoiceBy"));
                    stmt.setString(31, requestBody.getString("payeeBy"));
                    stmt.setString(32, requestBody.getString("reviewBy"));
                    stmt.setString(33, requestBody.getString("mainGoodsName"));
                    stmt.setString(34, requestBody.getString("goodsTaxItem"));
                    stmt.setString(35, requestBody.getString("redNoticeNo"));
                    stmt.setString(36, requestBody.getString("submitFlag"));
                    stmt.setString(37, requestBody.getString("checkCode"));
                    stmt.setString(38, requestBody.getString("importProveNo"));
                    stmt.setString(39, requestBody.getString("commodityInspectionNo"));
                    stmt.setString(40, requestBody.getString("limitCount"));
                    stmt.setString(41, requestBody.getString("tonnage"));
                    stmt.setString(42, requestBody.getString("industryCode"));
                    stmt.setString(43, requestBody.getString("tel"));
                    stmt.setString(44, requestBody.getString("factoryName"));
                    stmt.setString(45, requestBody.getString("sendBatchNo"));
                    stmt.setString(46, requestBody.getString("brandCode"));
                    stmt.setString(47, requestBody.getString("identityCode"));
                    stmt.setString(48, requestBody.getString("modelCode"));
                    stmt.setString(49, requestBody.getString("modelYear"));
                    stmt.setString(50, requestBody.getString("modelVersion"));
                    stmt.setString(51, requestBody.getString("interiorCode"));
                    stmt.setString(52, requestBody.getString("colorCode"));
                    stmt.setString(53, requestBody.getString("prList"));
                    stmt.setString(54, requestBody.getString("dealerNetCode"));
                    stmt.setString(55, requestBody.getString("regionCode"));
                    stmt.setString(56, requestBody.getString("invoiceType"));
                    stmt.setString(57, requestBody.getString("salesValidFlag"));
                    stmt.setString(58, requestBody.getString("subId"));

                    // 添加批处理
                    stmt.addBatch();
                    // 每隔一定量的批次执行，避免内存过高
                    if (i % 50 == 0) {
                        stmt.executeBatch();    // 执行批处理
                        stmt.clearBatch();     // 清空批处理
                    }
                }
                // 最后一批可能未满的批次提交
                stmt.executeBatch();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将发票信息插入到Mysql
     *
     * @param jsonString
     */
    public static void insertNGAInvoice(String jsonString) {
        JSONObject jsonObj = JSON.parseObject(jsonString);
        JSONObject requestHead = jsonObj.getJSONObject("requestHead");
        JSONArray dataArray = jsonObj.getJSONObject("requestBody").getJSONArray("data");
        JSONObject requestBody = dataArray.getJSONObject(0);

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {

            String sql = "insert into NGAInvoceInfo " +
                    "(requestId,sourceSystem,targetSystem,serviceName,transTime,brandCode,companyCode,dealerCode,dealerName," +
                    "ngaDptInvoiceNo,merOrderId,merCustId,custName,custTel,custIdNo,commissionNo,vin,identityCode,modelCode," +
                    "modelName,modelYear,modelVersion,interiorCode,prList,colorCode,vehicleType,factoryPlateModel,producePlace," +
                    "certNo,importProveNo,commodityInspectionNo,engineNo,tonnage,passengerLimitNum,isLoan,invoiceType," +
                    "invoiceCode,invoiceNo,invoiceDate,sellerName,sellerPhone,sellerTaxNumber,sellerAccount,sellerAddress" +
                    ",sellerBank,invoiceIdNo,invoiceName,invoiceTel,invoiceTaxNo,totalAmount,rebateAmount,totalTaxAmount" +
                    ",taxFreeAmount,taxAmount,taxRate,invoiceNum,taxClassCode,taxClassShortName,taxOfficeCode," +
                    "intactCertificateNum,invoiceStatus,originalNgaDptInvoiceNo,warrantySign) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                    "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, requestHead.getString("requestId"));
                stmt.setString(2, requestHead.getString("sourceSystem"));
                stmt.setString(3, requestHead.getString("targetSystem"));
                stmt.setString(4, requestHead.getString("serviceName"));
                stmt.setString(5, requestHead.getString("transTime"));

                stmt.setString(6, requestBody.getString("brandCode"));
                stmt.setString(7, requestBody.getString("companyCode"));
                stmt.setString(8, requestBody.getString("dealerCode"));
                stmt.setString(9, requestBody.getString("dealerName"));
                stmt.setString(10, requestBody.getString("ngaDptInvoiceNo"));
                stmt.setString(11, requestBody.getString("merOrderId"));
                stmt.setString(12, requestBody.getString("merCustId"));
                stmt.setString(13, requestBody.getString("custName"));
                stmt.setString(14, requestBody.getString("custTel"));
                stmt.setString(15, requestBody.getString("custIdNo"));
                stmt.setString(16, requestBody.getString("commissionNo"));
                stmt.setString(17, requestBody.getString("vin"));
                stmt.setString(18, requestBody.getString("identityCode"));
                stmt.setString(19, requestBody.getString("modelCode"));
                stmt.setString(20, requestBody.getString("modelName"));
                stmt.setString(21, requestBody.getString("modelYear"));
                stmt.setString(22, requestBody.getString("modelVersion"));
                stmt.setString(23, requestBody.getString("interiorCode"));
                stmt.setString(24, requestBody.getString("prList"));
                stmt.setString(25, requestBody.getString("colorCode"));
                stmt.setString(26, requestBody.getString("vehicleType"));
                stmt.setString(27, requestBody.getString("factoryPlateModel"));
                stmt.setString(28, requestBody.getString("producePlace"));
                stmt.setString(29, requestBody.getString("certNo"));
                stmt.setString(30, requestBody.getString("importProveNo"));
                stmt.setString(31, requestBody.getString("commodityInspectionNo"));
                stmt.setString(32, requestBody.getString("engineNo"));
                stmt.setString(33, requestBody.getString("tonnage"));
                stmt.setString(34, requestBody.getString("passengerLimitNum"));
                stmt.setString(35, requestBody.getString("isLoan"));
                stmt.setString(36, requestBody.getString("invoiceType"));
                stmt.setString(37, requestBody.getString("invoiceCode"));
                stmt.setString(38, requestBody.getString("invoiceNo"));
                stmt.setString(39, requestBody.getString("invoiceDate"));
                stmt.setString(40, requestBody.getString("sellerName"));
                stmt.setString(41, requestBody.getString("sellerPhone"));
                stmt.setString(42, requestBody.getString("sellerTaxNumber"));
                stmt.setString(43, requestBody.getString("sellerAccount"));
                stmt.setString(44, requestBody.getString("sellerAddress"));
                stmt.setString(45, requestBody.getString("sellerBank"));
                stmt.setString(46, requestBody.getString("invoiceIdNo"));
                stmt.setString(47, requestBody.getString("invoiceName"));
                stmt.setString(48, requestBody.getString("invoiceTel"));
                stmt.setString(49, requestBody.getString("invoiceTaxNo"));
                stmt.setString(50, requestBody.getString("totalAmount"));
                stmt.setString(51, requestBody.getString("rebateAmount"));
                stmt.setString(52, requestBody.getString("totalTaxAmount"));
                stmt.setString(53, requestBody.getString("taxFreeAmount"));
                stmt.setString(54, requestBody.getString("taxAmount"));
                stmt.setString(55, requestBody.getString("taxRate"));
                stmt.setString(56, requestBody.getString("invoiceNum"));
                stmt.setString(57, requestBody.getString("taxClassCode"));
                stmt.setString(58, requestBody.getString("taxClassShortName"));
                stmt.setString(59, requestBody.getString("taxOfficeCode"));
                stmt.setString(60, requestBody.getString("intactCertificateNum"));
                stmt.setString(61, requestBody.getString("invoiceStatus"));
                stmt.setString(62, requestBody.getString("originalNgaDptInvoiceNo"));
                stmt.setString(63, requestBody.getString("warrantySign"));

                stmt.executeUpdate();

            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
